import pymysql
from pymysql import cursors

conn = pymysql.connect(
    host='rm-2ze536m1b6gp2o50z5o.mysql.rds.aliyuncs.com',
    # host='localhost',
    port=3306,
    user='root',
    passwd='Root12345',
    db='assistant',
    charset='utf8',
    cursorclass=pymysql.cursors.DictCursor
)

def query_user_no_exists(username):
    sql = "select * from sys_user where username = %s"
    cur = conn.cursor()
    cur.execute(sql,[username])
    result = cur.fetchone()
    if result is None:
        return True
    else:
        return False

def add_user(username,password):
    sql = "insert into sys_user (username,password) values (%s,%s)"
    cur = conn.cursor()
    cur.execute(sql,[username,password])
    conn.commit()

def select_user(username):
    sql = "select * from sys_user where username = %s"
    cur = conn.cursor()
    cur.execute(sql,[username])
    res = cur.fetchone()
    return res

def query_message_by_user_id(user_id):
    sql = "select * from chat_message where user_id = %s order by message_time asc"
    cur = conn.cursor()
    cur.execute(sql, [user_id])
    # 字典类型的列表[{},{},{}]
    list = cur.fetchall()
    return list

def query_user(username):
    sql = "select username from sys_user where username = %s"
    cur = conn.cursor()
    cur.execute(sql, [username])
    result = cur.fetchone()
    if result is not None:
        return True
    else:
        return False

def update_message(username,password):
    sql = "update sys_user set password = %s where username = %s"
    cur = conn.cursor()
    cur.execute(sql, [password, username])
    conn.commit()
    return True

def query_user_password(username):
    sql = "select * from sys_user where username = %s"
    cur = conn.cursor()
    cur.execute(sql, [username])
    result = cur.fetchone()
    return result

def insert_message(user_id,message,role,message_time):
    sql = "insert into chat_message (user_id, message, role,message_time) values (%s,%s,%s,%s)"
    cur = conn.cursor()
    cur.execute(sql,[user_id,message,role,message_time])
    conn.commit()
    return True

def insert_work_message(user_id,message,role,message_time):
    sql = "insert into chat_work_message (user_id, message, role, message_time) values (%s,%s,%s,%s)"
    cur = conn.cursor()
    cur.execute(sql,[user_id,message,role,message_time])
    conn.commit()
    return True

def insert_emotion_message(user_id,message,role,message_time):
    sql = "insert into chat_emotion_message (user_id, message, role, message_time) values (%s,%s,%s,%s)"
    cur = conn.cursor()
    cur.execute(sql,[user_id,message,role,message_time])
    conn.commit()
    return True
